package gorm_clause

import (
	"encoding/json"
	"errors"
	"fmt"
	"reflect"
	"regexp"
	"strings"

	"github.com/spf13/cast"

	"github.com/cloudwego/kitex/pkg/klog"

	"gorm.io/gorm"
	"gorm.io/gorm/clause"
)

const (
	OperatorEq              = "eq"
	OperatorNotEq           = "not_eq"
	OperatorIn              = "in"
	OperatorNotIn           = "not_in"
	OperatorContains        = "contains"
	OperatorNotContains     = "not_contains"
	OperatorRangeBetween    = "range_between"
	OperatorNotRangeBetween = "not_range_between"
	OperatorStart           = "starts_with"
	OperatorNotStart        = "not_starts_with"
	OperatorEnd             = "ends_with"
	OperatorNotEnd          = "not_ends_with"
	OperatorInTimeRange     = "in_time_range"
	OperatorNotInTimeRange  = "not_in_time_range"
	OperatorLessOrEqual     = "less_or_equal"
	OperatorLess            = "less"
	OperatorGreaterOrEqual  = "greater_or_equal"
	OperatorGreater         = "greater"
)

// SearchFilterCondition 定义查询条件的结构
type SearchFilterCondition struct {
	Field    string      `json:"field"`
	Operator string      `json:"operator"`
	Value    interface{} `json:"value"`
}

type Filter struct {
	Or []FilterAnd `json:"or"`
}

type FilterAnd struct {
	And []SearchFilterCondition `json:"and"`
}

type From struct {
	Tables []clause.Table
	Joins  []clause.Join
}

func BuildGormDBClause(db *gorm.DB, filterJson string) error {
	if db == nil || len(filterJson) == 0 {
		return errors.New("empty db or conditions")
	}
	filter := Filter{}
	if err := json.Unmarshal([]byte(filterJson), &filter); err != nil {
		return errors.New("json unmarshal error")
	}
	return GormDBAddClause(db, filter, From{})
}

func GormDBAddClause(db *gorm.DB, filter Filter, from From) error {
	if db == nil || len(filter.Or) == 0 {
		return errors.New("empty db or conditions")
	}
	if from.Tables != nil {
		db.Statement.AddClause(
			clause.From{
				Tables: from.Tables,
				Joins:  from.Joins,
			},
		)
	}
	// todo: 📢注意：这里的 expr 中的 field 为了实现复杂的查询，是通过 fmt.Sprintf 拼接的，存在 sql 注入的风险，需要上层保证字段的合法性
	for _, or := range filter.Or {
		for _, cond := range or.And {
			var (
				expr string
				vars []any
			)
			switch cond.Operator {
			case OperatorEq:
				expr, vars, _ = handleOperatorEq(cond)
			case OperatorNotEq:
				expr, vars, _ = handleOperatorNotEq(cond)
			case OperatorIn:
				exp, value, _ := handleOperatorIn(cond)
				expr = exp
				vars = append(vars, value) // nolint
			case OperatorNotIn:
				exp, value, _ := handleOperatorNotIn(cond)
				expr = exp
				vars = append(vars, value) // nolint
			case OperatorContains:
				expr, vars, _ = handleOperatorContains(cond)
			case OperatorNotContains:
				expr, vars, _ = handleOperatorNotContains(cond)
			case OperatorRangeBetween:
				expr, vars, _ = handleOperatorRangeBetween(cond)
			case OperatorNotRangeBetween:
				expr, vars, _ = handleOperatorNotRangeBetween(cond)
			case OperatorStart, OperatorNotEnd:
				expr, vars, _ = handleOperatorStart(cond)
			case OperatorEnd, OperatorNotStart:
				expr, vars, _ = handleOperatorEnd(cond)
			case OperatorInTimeRange:
				expr, vars, _ = handleOperatorInTime(cond)
			case OperatorNotInTimeRange:
				expr, vars, _ = handleOperatorNotInTime(cond)
			case OperatorLessOrEqual:
				expr, vars, _ = handleOperatorExpr(cond, "<=")
			case OperatorLess:
				expr, vars, _ = handleOperatorExpr(cond, "<")
			case OperatorGreaterOrEqual:
				expr, vars, _ = handleOperatorExpr(cond, ">=")
			case OperatorGreater:
				expr, vars, _ = handleOperatorExpr(cond, ">")

				// 扩展更多的操作符
			}
			if len(expr) == 0 {
				continue
			}
			db.Statement.AddClause(
				clause.Where{
					Exprs: []clause.Expression{
						clause.Expr{SQL: expr, Vars: vars},
					},
				},
			)
		}
	}
	return nil
}

func handleOperatorEq(cond SearchFilterCondition) (expr string, vars []any, err error) {
	// 开关类型
	var jsonField string
	jsonField, err = getJsonField(cond.Field)
	if err != nil {
		klog.Errorf("get json field err: %v", err)
		return expr, nil, err
	}
	if reflect.TypeOf(cond.Value) == reflect.TypeOf(true) {
		if len(jsonField) > 0 {
			expr = fmt.Sprintf("JSON_VALID(%s) AND %s = %v", jsonField, cond.Field, cast.ToBool(cond.Value))
		} else {
			expr = fmt.Sprintf("%s = %v", cond.Field, cast.ToBool(cond.Value))
		}
	} else {
		if len(jsonField) > 0 {
			expr = fmt.Sprintf("JSON_VALID(%s) AND %s = ?", jsonField, cond.Field)
		} else {
			expr = fmt.Sprintf("%s = ?", cond.Field)
		}
		vars = append(vars, cond.Value)
	}
	return expr, vars, nil
}

func handleOperatorNotEq(cond SearchFilterCondition) (expr string, vars []any, err error) {
	// 开关类型
	var jsonField string
	jsonField, err = getJsonField(cond.Field)
	if err != nil {
		klog.Errorf("get json field err: %v", err)
		return expr, nil, err
	}
	if reflect.TypeOf(cond.Value) == reflect.TypeOf(true) {
		if len(jsonField) > 0 {
			expr = fmt.Sprintf("JSON_VALID(%s) AND %s != %v", jsonField, cond.Field, cast.ToBool(cond.Value))
		} else {
			expr = fmt.Sprintf("%s != %v", cond.Field, cast.ToBool(cond.Value))
		}
	} else {
		if len(jsonField) > 0 {
			expr = fmt.Sprintf("JSON_VALID(%s) AND %s != ?", jsonField, cond.Field)
		} else {
			expr = fmt.Sprintf("%s != ?", cond.Field)
		}
		vars = append(vars, cond.Value)
	}
	return expr, vars, nil
}

func handleOperatorIn(cond SearchFilterCondition) (string, []any, error) {
	// 确保 cond.Value 是切片类型
	values, ok := cond.Value.([]any)
	if !ok {
		klog.Errorf("value is not a slice")
		return "", nil, errors.New("value is not a slice")
	}
	return fmt.Sprintf("%s in (?)", cond.Field), values, nil
}

func handleOperatorNotIn(cond SearchFilterCondition) (string, []any, error) {
	// 确保 cond.Value 是切片类型
	values, ok := cond.Value.([]any)
	if !ok {
		klog.Errorf("value is not a slice")
		return "", nil, errors.New("value is not a slice")
	}
	return fmt.Sprintf("%s not in (?)", cond.Field), values, nil
}

func handleOperatorContains(cond SearchFilterCondition) (expr string, vars []any, err error) {
	expr = fmt.Sprintf("%s COLLATE utf8mb4_general_ci like ?", cond.Field)
	if isHasJsonFunc(cond.Field) {
		var jsonField string
		jsonField, err = getJsonField(cond.Field)
		if err != nil {
			klog.Errorf("get json field err: %v", err)
			return expr, nil, err
		}
		expr = fmt.Sprintf("JSON_VALID(%s) AND %s COLLATE utf8mb4_general_ci like ? ", jsonField, cond.Field)
	}
	vars = append(vars, fmt.Sprintf("%%%s%%", cond.Value))
	return expr, vars, nil
}

func handleOperatorNotContains(cond SearchFilterCondition) (expr string, vars []any, err error) {
	expr = fmt.Sprintf("%s COLLATE utf8mb4_general_ci not like ? ", cond.Field)
	if isHasJsonFunc(cond.Field) {
		var jsonField string
		jsonField, err = getJsonField(cond.Field)
		if err != nil {
			klog.Errorf("get json field err: %v", err)
			return expr, nil, err
		}
		expr = fmt.Sprintf("JSON_VALID(%s) AND %s COLLATE utf8mb4_general_ci not like ? ", jsonField, cond.Field)
	}
	vars = append(vars, fmt.Sprintf("%%%s%%", cond.Value))
	return expr, vars, nil
}

func handleOperatorRangeBetween(cond SearchFilterCondition) (expr string, vars []any, err error) {
	values, ok := cond.Value.([]any)
	if !ok || len(values) != 2 {
		klog.Errorf("range betweem value length is not 2")
		return expr, nil, errors.New("range value length is not 2")
	}
	expr = fmt.Sprintf("%s between ? and ?", cond.Field)
	vars = append(vars, values[0], values[1])
	return expr, vars, nil
}

func handleOperatorNotRangeBetween(cond SearchFilterCondition) (expr string, vars []any, err error) {
	values, ok := cond.Value.([]any)
	if !ok || len(values) != 2 {
		klog.Errorf("not range betweem value length is not 2")
		return expr, nil, errors.New("range value length is not 2")
	}
	expr = fmt.Sprintf("%s not between ? and ?", cond.Field)
	vars = append(vars, values[0], values[1])
	return expr, vars, nil
}

func handleOperatorStart(cond SearchFilterCondition) (expr string, vars []any, err error) {
	values, ok := cond.Value.(string)
	if !ok {
		klog.Errorf("start not string type")
		return expr, nil, errors.New("start not string type")
	}
	expr = fmt.Sprintf("%s like ?", cond.Field)
	vars = append(vars, fmt.Sprintf("%%%s", values))
	return expr, vars, nil
}

func handleOperatorEnd(cond SearchFilterCondition) (expr string, vars []any, err error) {
	values, ok := cond.Value.(string)
	if !ok {
		klog.Errorf("end not string type")
		return expr, nil, errors.New("start not string type")
	}
	expr = fmt.Sprintf("%s like ?", cond.Field)
	vars = append(vars, fmt.Sprintf("%s%%", values))
	return expr, vars, nil
}

func handleOperatorInTime(cond SearchFilterCondition) (expr string, vars []any, err error) {
	values, ok := cond.Value.(string)
	if !ok {
		klog.Errorf("end not string type")
		return expr, nil, errors.New("start not string type")
	}
	expr = fmt.Sprintf("%s > ?", cond.Field)
	vars = append(vars, values)
	return expr, vars, nil
}

func handleOperatorNotInTime(cond SearchFilterCondition) (expr string, vars []any, err error) {
	values, ok := cond.Value.(string)
	if !ok {
		klog.Errorf("end not string type")
		return expr, nil, errors.New("start not string type")
	}
	expr = fmt.Sprintf("%s < ?", cond.Field)
	vars = append(vars, values)
	return expr, vars, nil
}

func handleOperatorExpr(cond SearchFilterCondition, e string) (expr string, vars []any, err error) {
	expr = fmt.Sprintf("%s %s ?", cond.Field, e)
	if isHasJsonFunc(cond.Field) {
		jsonField, err := getJsonField(cond.Field)
		if err != nil {
			klog.Errorf("get json field err: %v", err)
			return expr, nil, err
		}
		expr = fmt.Sprintf("JSON_VALID(%s) AND %s %s ?", jsonField, cond.Field, e)
	}
	vars = append(vars, cond.Value)
	return expr, vars, nil
}

func isHasJsonFunc(field string) bool {
	return strings.HasPrefix(field, "JSON_UNQUOTE") || strings.HasPrefix(
		field, "JSON_EXTRACT",
	) || strings.HasPrefix(field, "JSON_CONTAINS")
}

func getJsonField(field string) (string, error) {
	if !isHasJsonFunc(field) {
		return "", nil
	}
	// 正则表达式，用于提取最内层括号内的内容
	re := regexp.MustCompile(`\([^()]+\)`)
	// 匹配并提取结果
	match := re.FindStringSubmatch(field)
	if len(match) > 0 {
		// 输出匹配到的最内层括号内容
		content := strings.Trim(match[0], "()")
		return strings.Split(content, ",")[0], nil
	}
	return "", errors.New("json function is not matched")
}
